Use HuongRung_Team_19
--Lay danh sach mon ab theo tung thuc don
CREATE PROCEDURE sp_ChiTietMonAnTheoThucDon
(
	@MaThucDon int
)
AS
BEGIN
	SELECT ma.MaMonAn, ma.TenMonAn
	FROM MonAn ma, ChiTietThucDonMonAn ct
	WHERE ma.MaMonAn = ct.MaMonAn and ct.MaThucDon = @MaThucDon and ct.TinhTrang = 1
END 
GO

-----------------------------
--Lay danh sach thuc don
CREATE PROCEDURE sp_LayDanhSachThucDon
AS
BEGIN
	SELECT *
	FROM ThucDon
END 
GO
--============================
--Them thuc don
CREATE PROCEDURE sp_ThemThucDon
(
	@TenMonAn nvarchar(50),
	@TinhTrang int
)
AS
BEGIN
	BEGIN TRANSACTION
		INSERT INTO dbo.ThucDon(TenThucDon, NgayLap, TinhTrang) VALUES (@TenMonAn, GETDATE(),@TinhTrang)
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
------------------------------
--Cap nhat thuc don
CREATE PROCEDURE sp_CapNhatThucDon
(
	@MaThucDon int,
	@TenThucDon nvarchar(50),
	@TinhTrang int
)
AS
BEGIN
	BEGIN TRANSACTION
		UPDATE dbo.ThucDon SET TenThucDon = @TenThucDon, TinhTrang = @TinhTrang WHERE MaThucDon = @MaThucDon
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
------------------------------
--huy mon an
CREATE PROCEDURE sp_HuyThucDon
(
	@MaThucDon int
)
AS
BEGIN
	BEGIN TRANSACTION
		UPDATE dbo.ThucDon SET TinhTrang = 0 WHERE MaThucDon = @MaThucDon
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
------------------------------
--Lấy danh sách món ăn rút gọn
CREATE PROCEDURE sp_LayDanhSachMonAnRG
AS
BEGIN
	SELECT MaMonAn,TenMonAn
	FROM MonAn
END 
GO
--Lấy danh sách món ăn rút gọn
CREATE PROCEDURE sp_DSMonAnTheoThucDon
(
	@MaThucDon int
)
AS
BEGIN
	SELECT m.MaMonAn,TenMonAn
	FROM MonAn m, ChiTietThucDonMonAn ct
	WHERE ct.MaThucDon = @MaThucDon AND ct.MaMonAn = m.MaMonAn and ct.TinhTrang = 1
END 
GO
------------------------------
--them mon an vao thuc don
CREATE PROCEDURE sp_ThemMonAnVaoThucDon
(
	@MaThucDon INT,
	@MaMonAn INT
)
AS
BEGIN
	BEGIN TRANSACTION
		DECLARE @STT INT
		SET @STT = (SELECT STT FROM dbo.ChiTietThucDonMonAn ct, dbo.MonAn ma, dbo.ThucDon td 
			WHERE ma.MaMonAn = ct.MaMonAn AND ct.MaThucDon = td.MaThucDon AND ct.MaThucDon = @MaThucDon AND ct.MaMonAn = @MaMonAn)
		IF @STT IS NULL
		BEGIN
			INSERT INTO dbo.ChiTietThucDonMonAn
					( MaThucDon ,
					  MaMonAn ,
					  TinhTrang
					)
			VALUES  ( @MaThucDon , -- MaThucDon - int
					  @MaMonAn , -- MaMonAn - int
					  1  -- TinhTrang - int
					)
		END
		ELSE
		BEGIN
			UPDATE dbo.ChiTietThucDonMonAn SET TinhTrang = 1 WHERE STT = @STT
		END
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
-----------------------------
--Bot nguyen lieu cho mon an
CREATE PROCEDURE sp_BotMonAnCuaThucDon
(
	@MaThucDon INT,
	@MaMonAn INT
)
AS
BEGIN
	BEGIN TRANSACTION
		DECLARE @STT INT
		SET @STT = (SELECT STT FROM dbo.ChiTietThucDonMonAn ct, dbo.MonAn ma, dbo.ThucDon nl 
			WHERE ma.MaMonAn = ct.MaMonAn AND ct.MaThucDon = nl.MaThucDon AND ct.MaThucDon = @MaThucDon AND ct.MaMonAn = @MaMonAn)
		UPDATE dbo.ChiTietThucDonMonAn SET TinhTrang = 0 WHERE STT = @STT
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
--Lay danh sach thuc don co the sung dung
CREATE PROCEDURE sp_DSMaThucDonTonTai
AS
BEGIN
	SELECT MaThucDon
	FROM ThucDon
	WHERE TinhTrang = 1
END 
GO
-----------------------
CREATE PROCEDURE sp_DSMaThucDonTonTai
AS
BEGIN
	SELECT MaThucDon
	FROM ThucDon
	WHERE TinhTrang = 1
END 
GO
-------------------------
--Lap Phieu Moi
CREATE PROCEDURE sp_LapPhieuYCMoi
(
	@MaLoai INT
)
AS
BEGIN
	BEGIN TRANSACTION
		DECLARE @STT INT
		INSERT INTO PhieuYeuCau(NgayLap,GhiChu,BoPhan,MaNV,NgayGiao,LoaiPhieu)
		VALUES (GETDATE(),null,null,null,null,@MaLoai)
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
		Select top 1 MaPhieuYC FROM PhieuYeuCau ORDER BY MaPhieuYC DESC
	COMMIT
END
GO
--------------------
--Them chi tiet cho ma phieu yeu cau
CREATE PROCEDURE sp_ThemChiTietPhieuYeuCau
(
	@MaPhieu INT,
	@MaSP int,
	@Soluong int
)
AS
BEGIN
	BEGIN TRANSACTION
		DECLARE @STT INT
		INSERT INTO ChiTietPhieuYeuCauSanPham(MaPhieuYC,MaSP,SoLuong)
		VALUES (@MaPhieu,@MaSP,@Soluong)
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
--Xoa phieu yeu cau
CREATE PROCEDURE sp_XoaPhieuYC
(
	@MaPhieu int
)
AS
BEGIN
	BEGIN TRANSACTION
		DELETE FROM PhieuYeuCau WHERE MaPhieuYC = @MaPhieu
		DELETE FROM ChiTietPhieuYeuCauSanPham WHERE MaPhieuYC = @MaPhieu
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO